﻿using System;
using System.Collections;
using System.Data;
using System.Configuration;
using System.Web;
using System.IO;
using System.Text;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.Util;
using System.Windows.Forms;
using NPOI.SS.Util;
using NPOI.SS.UserModel;
using  FL.Common.ExcelExportNpoi.DataClass;

namespace FL.Common.ExcelExportNpoi
{
public class NPOIExcelHelper
{

    /// <summary>
    /// DataTable导出到Excel文件
    /// </summary>
    /// <param name="dtSource">源DataTable</param>
    /// <param name="strHeaderText">表头文本</param>
    /// <param name="strFileName">保存位置</param>
    public static void DataTableToExcel(DataTable dtSource, ExcelSet excelSet,DataTable dtConfig, string strFileName)
    {
        using (MemoryStream ms = DataTableToExcel(dtSource, excelSet,dtConfig))
        {
            using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
        }
    }
    /// <summary>
    /// DataGridView导出到Excel文件
    /// </summary>
    /// <param name="dtSource">源DataTGridview</param>
    /// <param name="strHeaderText">表头文本</param>
    /// <param name="strFileName">保存位置</param>
    public static void DataGridViewToExcel(DataGridView myDgv, string strHeaderText, string strFileName)
    {
        using (MemoryStream ms = DataGridViewToExcel(myDgv, strHeaderText))
        {
            using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
        }
    }

    /// <summary>
    /// DataTable导出到Excel的MemoryStream
    /// </summary>
    /// <param name="dtSource">源DataTable</param>
    /// <param name="strHeaderText">表头文本</param>
    public static MemoryStream DataTableToExcel(DataTable dtSource, ExcelSet excelSet,DataTable dtConfig)
    {
        if (dtSource == null)
        {
            return null;
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        //HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
        //sheet页面名称
       
        HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(excelSet.sheetName);
        workbook.SetRepeatingRowsAndColumns(0, 0, 0, 0, 2);//重复打印前3行
        sheet.FitToPage = false;
        sheet.PrintSetup.PaperSize = 9;//A4纸张
        sheet.PrintSetup.Landscape = true;//横向打印（默认为false即竖向）
        sheet.SetMargin(MarginType.LeftMargin, 0);
        sheet.SetMargin(MarginType.RightMargin, 0);
        sheet.SetMargin(MarginType.TopMargin, (double)0.4);//奇葩的基数是2.5
        sheet.SetMargin(MarginType.BottomMargin, (double)0.2);
        sheet.PrintSetup.HeaderMargin = (double)0.32;
        sheet.PrintSetup.FooterMargin = (double)0.32;
        #region 右击文件 属性信息
        {
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI";
            workbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = "文件作者信息"; //填加xls文件作者信息
            si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
            si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
            si.Comments = "作者信息"; //填加xls文件作者信息
            si.Title = "标题信息"; //填加xls文件标题信息
            si.Subject = "主题信息";//填加文件主题信息
            si.CreateDateTime = System.DateTime.Now;
            workbook.SummaryInformation = si;
        }
        #endregion

        HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

        //取得列宽
        int[] arrColWidth = new int[dtSource.Columns.Count];
        foreach (DataColumn item in dtSource.Columns)
        {
            arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
        }
        for (int i = 0; i < dtSource.Rows.Count; i++)
        {
            for (int j = 0; j < dtSource.Columns.Count; j++)
            {
                int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                if (intTemp > arrColWidth[j])
                {
                    arrColWidth[j] = intTemp;
                }
            }
        }
        int rowIndex = 0;
        //添加信息内容样式
        HSSFCellStyle detailStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        //headStyle.Alignment = CellHorizontalAlignment.Center;
        HSSFFont detailfont = (HSSFFont)workbook.CreateFont();
        detailfont.FontHeightInPoints = 9;
        detailfont.Boldweight = 200;
        detailStyle.SetFont(detailfont);
        detailStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; // NPOI.SS.UserModel.BorderStyle.Thin;//下边框
        detailStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;//  NPOI.SS.UserModel.BorderStyle.Thin;//左边框
        detailStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; // NPOI.SS.UserModel.BorderStyle.Thin;//上边框
        detailStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; // NPOI.SS.UserModel.BorderStyle.Thin;//右边框
        detailStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
        int firstrow = 0;//起始行
        int endrow = 0;//起始行
        //保存合计行的数据
        Hashtable tab = new Hashtable();
        foreach (DataRow row in dtSource.Rows)
        {
            if (rowIndex == 65535 || rowIndex == 0)
            {
                if (rowIndex != 0)
                {
                    sheet = (HSSFSheet)workbook.CreateSheet();
                }
                #region 表头标题
                {
                    if (excelSet != null)
                    {
                        if (excelSet.isNeedHeader)
                        {
                            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
                            headerRow.HeightInPoints = 25;
                            //头部名称
                            headerRow.CreateCell(0).SetCellValue(excelSet.strHeaderText);
                            //CellRangeAddress合并单元格四个参数为：起始行，结束行，起始列，结束列
                            CellRangeAddress headregion = new CellRangeAddress(firstrow, endrow, 0, dtSource.Columns.Count - 1);
                            sheet.AddMergedRegion(headregion);
                            if (excelSet.isFreezeHeader)
                            {
                                sheet.CreateFreezePane(0, 1);//冻结窗格
                            }

                            HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                            headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//垂直居中
                            //headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//  NPOI.SS.UserModel.BorderStyle.Thin;//下边框
                            //headStyle.BorderLeft =  NPOI.SS.UserModel.BorderStyle.Thin;//左边框
                            headStyle.BorderTop =  NPOI.SS.UserModel.BorderStyle.Thin;//上边框
                            //headStyle.BorderRight =  NPOI.SS.UserModel.BorderStyle.Thin;//右边框
                            HSSFFont font = (HSSFFont)workbook.CreateFont();
                            font.FontHeightInPoints = excelSet.headerFont;
                            font.Boldweight = 700;
                            headStyle.SetFont(font);
                            //合并单元格后边框的颜色处理
                            for (int i = headregion.FirstRow; i <= headregion.LastRow; i++)
                            {
                                IRow irow = HSSFCellUtil.GetRow(i, sheet);
                                for (int j = headregion.FirstColumn; j <= headregion.LastColumn; j++)
                                {
                                    ICell singleCell = HSSFCellUtil.GetCell(irow, (short)j);
                                    singleCell.CellStyle = headStyle;
                                }
                            }
                            //判断是否需要二级表头
                            if (excelSet.isNeedSecondHeader)
                            {
                                rowIndex += 1;
                                firstrow += 1;
                                endrow += 1;

                                #region 二级表头标题附加信息
                                HSSFRow headerRow2 = (HSSFRow)sheet.CreateRow(rowIndex);
                                headerRow2.HeightInPoints = 20;
                                headerRow2.CreateCell(0).SetCellValue(excelSet.strSecondHeaderText);

                                //CellRangeAddress合并单元格四个参数为：起始行，结束行，起始列，结束列
                                CellRangeAddress region = new CellRangeAddress(firstrow, endrow, 0, dtSource.Columns.Count - 1);
                                sheet.AddMergedRegion(region);
                                if (excelSet.isFreezeSecondHeader)
                                {
                                    sheet.CreateFreezePane(0, 2);//冻结窗格
                                }

                                HSSFCellStyle headStyle2 = (HSSFCellStyle)workbook.CreateCellStyle();
                                headStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center ;//垂直居中
                                headStyle2.BorderBottom =  NPOI.SS.UserModel.BorderStyle.Thin;//下边框
                                //headStyle2.BorderLeft =  NPOI.SS.UserModel.BorderStyle.Thin;//左边框
                                //headStyle2.BorderTop =  NPOI.SS.UserModel.BorderStyle.Thin;//上边框
                               // headStyle2.BorderRight =  NPOI.SS.UserModel.BorderStyle.Thin;//右边框
                                HSSFFont font2 = (HSSFFont)workbook.CreateFont();
                                font2.FontHeightInPoints = excelSet.secondHeaderFont;
                                font2.Boldweight = 400;
                                headStyle2.SetFont(font2);
                                //合并单元格后边框的颜色处理
                                for (int i = region.FirstRow; i <= region.LastRow; i++)
                                {
                                    IRow irow = HSSFCellUtil.GetRow(i, sheet);
                                    for (int j = region.FirstColumn; j <= region.LastColumn; j++)
                                    {
                                        ICell singleCell = HSSFCellUtil.GetCell(irow, (short)j);
                                        singleCell.CellStyle = headStyle2;
                                    }
                                }
                                #endregion
                            }
                            rowIndex += 1;
                        }
                        else
                        {
                            //firstrow += 1;
                            //endrow += 1;
                        }
                    }
                }
                #endregion

                #region 列头及样式
                {
                    HSSFCellStyle ColumnHeadStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                    //居中，两个必须合起来一起用
                    ColumnHeadStyle.VerticalAlignment = VerticalAlignment.Center;//水平居中
                    ColumnHeadStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//垂直居中
                    HSSFFont columnfont = (HSSFFont)workbook.CreateFont();
                    columnfont.FontHeightInPoints = 10;//字体大小
                    columnfont.Boldweight = 700;//字体的粗细
                    ColumnHeadStyle.SetFont(columnfont);
                    ColumnHeadStyle.BorderBottom =  NPOI.SS.UserModel.BorderStyle.Thin;//下边框
                    ColumnHeadStyle.BorderLeft =  NPOI.SS.UserModel.BorderStyle.Thin;//左边框
                    ColumnHeadStyle.BorderTop =  NPOI.SS.UserModel.BorderStyle.Thin;//上边框
                    ColumnHeadStyle.BorderRight =  NPOI.SS.UserModel.BorderStyle.Thin;//右边框
                    HSSFRow headerRow2 = (HSSFRow)sheet.CreateRow(rowIndex);
                    HSSFRow headerRow3=new HSSFRow();
                    if (excelSet.isFreezeSecondHeader)
                    {
                        sheet.CreateFreezePane(0, 3);//冻结窗格
                    }
                    if (excelSet.isMergeCell)
                    {
                        if (excelSet.isFreezeSecondHeader)
                        {
                            sheet.CreateFreezePane(0, 4);//冻结窗格
                        }
                        workbook.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3);//有合并列多加一行
                        rowIndex += 1;
                        headerRow3 = (HSSFRow)sheet.CreateRow(rowIndex);
                    }

                    foreach (DataColumn column in dtSource.Columns)
                    {

                        //有多列
                        //#region
                        if (excelSet.isMergeCell)
                        {
                            if (!string.IsNullOrEmpty(excelSet.MergeCellValue))
                            {
                               
                                string flag = "";
                                string ishave = "";

                                string mergeCellname = "";//合并列名称
                                int firstcell;//合并起始列
                                int endcell;//合并起始列
                                string[] s1 = excelSet.MergeCellValue.Split(';');
                                string[] s2;
                                string[] s3;
                                if (s1.Length > 0)
                                {
                                    #region 
                                    for (int n = 0; n < s1.Length; n++)
                                    {
                                        s2 = s1[n].Split(':');
                                        mergeCellname = s2[0];
                                        s3 = s2[1].Split(',');
                                        firstcell = int.Parse(s3[0]);
                                        endcell = int.Parse(s3[1]);
                                        if (column.Ordinal >= firstcell && column.Ordinal <= endcell)
                                        {
                                            //CellRangeAddress合并单元格四个参数为：起始行，结束行，起始列，结束列
                                            CellRangeAddress region = new CellRangeAddress(2, 2, firstcell, endcell);
                                            sheet.AddMergedRegion(region);
                                            CellRangeAddress region2 = new CellRangeAddress(3, 3, column.Ordinal, column.Ordinal);
                                            sheet.AddMergedRegion(region2);
                                            headerRow2.CreateCell(column.Ordinal).SetCellValue(mergeCellname);
                                            headerRow3.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                                            //设置列宽
                                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 300);
                                            //填充样式，边框
                                            for (int i = region.FirstRow; i <= region.LastRow; i++)
                                            {
                                                IRow irow = HSSFCellUtil.GetRow(i, sheet);
                                                for (int j = region.FirstColumn; j <= region.LastColumn; j++)
                                                {
                                                    ICell singleCell = HSSFCellUtil.GetCell(irow, (short)j);
                                                    singleCell.CellStyle = ColumnHeadStyle;
                                                }

                                            }
                                            //填充样式，边框
                                            for (int i = region2.FirstRow; i <= region2.LastRow; i++)
                                            {
                                                IRow irow = HSSFCellUtil.GetRow(i, sheet);
                                                for (int j = region2.FirstColumn; j <= region2.LastColumn; j++)
                                                {
                                                    ICell singleCell = HSSFCellUtil.GetCell(irow, (short)j);
                                                    singleCell.CellStyle = ColumnHeadStyle;
                                                }

                                            }
                                            ishave = "存在值";
                                        }
                                        else
                                        {
                                            flag = "存在列";
                                        }
                                    }
                                #endregion
                                }
                                else
                                {
                                    //CellRangeAddress合并单元格四个参数为：起始行，结束行，起始列，结束列
                                    CellRangeAddress region = new CellRangeAddress(2, 2, column.Ordinal, column.Ordinal);
                                    sheet.AddMergedRegion(region);
                                    headerRow2.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                                    sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 300);

                                    //填充样式，边框
                                    for (int i = region.FirstRow; i <= region.LastRow; i++)
                                    {
                                        IRow irow = HSSFCellUtil.GetRow(i, sheet);
                                        for (int j = region.FirstColumn; j <= region.LastColumn; j++)
                                        {
                                            ICell singleCell = HSSFCellUtil.GetCell(irow, (short)j);
                                            singleCell.CellStyle = ColumnHeadStyle;
                                        }
                                    }
                                }
                                if (!string.IsNullOrEmpty(flag)&&string.IsNullOrEmpty(ishave))
                                {
                                    //CellRangeAddress合并单元格四个参数为：起始行，结束行，起始列，结束列
                                    CellRangeAddress region = new CellRangeAddress(2, 3, column.Ordinal, column.Ordinal);
                                    sheet.AddMergedRegion(region);
                                    headerRow2.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                                    sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 300);

                                    //填充样式，边框
                                    for (int i = region.FirstRow; i <= region.LastRow; i++)
                                    {
                                        IRow irow = HSSFCellUtil.GetRow(i, sheet);
                                        for (int j = region.FirstColumn; j <= region.LastColumn; j++)
                                        {
                                            ICell singleCell = HSSFCellUtil.GetCell(irow, (short)j);
                                            singleCell.CellStyle = ColumnHeadStyle;
                                        }
                                    }
                                }
                            }
                        }
                        else
                        {
                            //CellRangeAddress合并单元格四个参数为：起始行，结束行，起始列，结束列
                            CellRangeAddress region = new CellRangeAddress(2, 2, column.Ordinal, column.Ordinal);
                            sheet.AddMergedRegion(region);
                            headerRow2.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 300);

                            //填充样式，边框
                            for (int i = region.FirstRow; i <= region.LastRow; i++)
                            {
                                IRow irow = HSSFCellUtil.GetRow(i, sheet);
                                for (int j = region.FirstColumn; j <= region.LastColumn; j++)
                                {
                                    ICell singleCell = HSSFCellUtil.GetCell(irow, (short)j);
                                    singleCell.CellStyle = ColumnHeadStyle;
                                }
                            }
                        }
                    }
                    
                }
                #endregion

                rowIndex += 1;
            }
            #region 填充内容
            HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
            foreach (DataColumn column in dtSource.Columns)
            {
                HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
                newCell.CellStyle = detailStyle;
                string drValue = row[column].ToString();
                #region 合计统计数叠加
                //add by zengtongchun 2016-07-04 合计统计数叠加 开始-----------------
                var str = drValue;
                //int.TryParse(str, out a);
                string text = drValue;
                if (str.Trim() == "0" || str.Trim() == "0.00")
                {
                    text = "";
                }
                int isText = 0;//默认文本
                for (int i = 0; i < dtConfig.Rows.Count; i++)
                {
                    //名称相同并且是数字的
                    if ((column.ToString().Replace("}", "").Replace("{", "").Trim() == dtConfig.Rows[i]["custom_name"].ToString()) && dtConfig.Rows[i]["export_type"].ToString()=="1")
                    {

                        isText = Convert.ToInt32(dtConfig.Rows[i]["export_type"]);
                        break;
                    }
                }
                //数字统计
                if (isText > 0)
                {
                    text = text == "" ? "0" : text;
                    if (tab.ContainsKey(column))
                    {
                        tab[column] = (Convert.ToDecimal(tab[column]) + Convert.ToDecimal(text)).ToString();
                    }
                    else
                    {
                        tab.Add(column, text);
                    }
                }
               
                //add by zengtongchun 2016-07-04 合计统计数叠加 结束-----------------
#endregion
                switch (column.DataType.ToString())
                {
                    case "System.String"://字符串类型
                        newCell.SetCellValue(text);
                        break;
                    case "System.DateTime"://日期类型
                        System.DateTime dateV;
                        System.DateTime.TryParse(text, out dateV);
                        newCell.SetCellValue(dateV);

                        newCell.CellStyle = dateStyle;//格式化显示
                        break;
                    case "System.Boolean"://布尔型
                        bool boolV = false;
                        bool.TryParse(text, out boolV);
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16"://整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(text, out intV);
                        if (intV!=0)
                        {
                            newCell.SetCellValue(intV);
                        }
                        
                        break;
                    case "System.Decimal"://浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(text, out doubV);
                        if (doubV != 0)
                        {
                            newCell.SetCellValue(doubV);
                        }
                        break;
                    case "System.DBNull"://空值处理
                        newCell.SetCellValue("");
                        break;
                    default:
                        newCell.SetCellValue("");
                        break;
                }
            }
            #endregion
            rowIndex++;
        }
        //合计行 add by zengtongchun 2016-07-04
        HSSFRow dataRowCount = (HSSFRow)sheet.CreateRow(rowIndex);
        foreach (DataColumn column in dtSource.Columns)
        {
            HSSFCell newCountCell = (HSSFCell)dataRowCount.CreateCell(column.Ordinal);
            newCountCell.CellStyle = detailStyle;
            double drValue =0;
            if (tab.ContainsKey(column))
            {
                drValue =Convert.ToDouble(tab[column]);
                
            }
            if (drValue != 0)
            {
                newCountCell.SetCellValue(drValue);
            }
            
            //switch (column.DataType.ToString())
            //{
            //    case "System.String"://字符串类型
            //        newCountCell.SetCellValue(drValue);
            //        break;
            //    case "System.DateTime"://日期类型
            //        System.DateTime dateV;
            //        System.DateTime.TryParse(drValue, out dateV);
            //        newCountCell.SetCellValue(dateV);

            //        newCountCell.CellStyle = dateStyle;//格式化显示
            //        break;
            //    case "System.Boolean"://布尔型
            //        bool boolV = false;
            //        bool.TryParse(drValue, out boolV);
            //        newCountCell.SetCellValue(boolV);
            //        break;
            //    case "System.Int16"://整型
            //    case "System.Int32":
            //    case "System.Int64":
            //    case "System.Byte":
            //        int intV = 0;
            //        int.TryParse(drValue, out intV);
                    
            //        newCountCell.SetCellValue(intV);
            //        break;
            //    case "System.Decimal"://浮点型
            //    case "System.Double":
            //        double doubV = 0;
            //        double.TryParse(drValue, out doubV);
            //        newCountCell.SetCellValue(doubV);
            //        break;
            //    case "System.DBNull"://空值处理
            //        newCountCell.SetCellValue("");
            //        break;
            //    default:
            //        newCountCell.SetCellValue("");
            //        break;
            //}
        }
        //合计行 add by zengtongchun 2016-07-04
        
        using (MemoryStream ms = new MemoryStream())
        {
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            //sheet.Dispose();
            //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
            return ms;
        }
    }

    public static  byte[] DataTableToExcel( ExcelSet excelSet,DataTable dtSource,DataTable dtConfig)
    {
        MemoryStream stream = DataTableToExcel(dtSource, excelSet, dtConfig);
        return stream.ToArray();
    }

    public static byte[] DataTableToExcel1(ExcelSet excelSet, DataTable dtSource, DataTable dtConfig)
    {
        return DataTableToExcel1(dtSource, excelSet, dtConfig).ToArray();
    }
    /// <summary>
    /// 重写列名 add by zengtongchun 
    /// </summary>
    /// <param name="nowColumnName"></param>
    /// <param name="dt"></param>
    /// <returns></returns>
    public static string ReWriteColumnName(string nowColumnName,DataTable dt)
    {

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            if (dt.Rows[i]["field"].ToString() == nowColumnName)
            {
                nowColumnName = dt.Rows[i]["custom_name"].ToString();
                break;
            }
        }
        return nowColumnName;
    }

    public static MemoryStream DataTableToExcel1(DataTable dtSource, ExcelSet excelSet, DataTable dtConfig)
    {
        if (dtSource == null)
        {
            return null;
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        //HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
        //sheet页面名称

        HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(excelSet.sheetName);
        workbook.SetRepeatingRowsAndColumns(0, 0, 0, 0, 2);//重复打印前3行
        sheet.FitToPage = false;
        sheet.PrintSetup.PaperSize = 9;//A4纸张
        sheet.PrintSetup.Landscape = true;//横向打印（默认为false即竖向）
        sheet.SetMargin(MarginType.LeftMargin, 0);
        sheet.SetMargin(MarginType.RightMargin, 0);
        sheet.SetMargin(MarginType.TopMargin, (double)0.4);//奇葩的基数是2.5
        sheet.SetMargin(MarginType.BottomMargin, (double)0.2);
        sheet.PrintSetup.HeaderMargin = (double)0.32;
        sheet.PrintSetup.FooterMargin = (double)0.32;
        #region 右击文件 属性信息
        {
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI";
            workbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = "文件作者信息"; //填加xls文件作者信息
            si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
            si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
            si.Comments = "作者信息"; //填加xls文件作者信息
            si.Title = "标题信息"; //填加xls文件标题信息
            si.Subject = "主题信息";//填加文件主题信息
            si.CreateDateTime = System.DateTime.Now;
            workbook.SummaryInformation = si;
        }
        #endregion

        HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

        //取得列宽
        int[] arrColWidth = new int[dtSource.Columns.Count];
        foreach (DataColumn item in dtSource.Columns)
        {
            arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
        }
        for (int i = 0; i < dtSource.Rows.Count; i++)
        {
            for (int j = 0; j < dtSource.Columns.Count; j++)
            {
                int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                if (intTemp > arrColWidth[j])
                {
                    arrColWidth[j] = intTemp;
                }
            }
        }
        int rowIndex = 0;
        //添加信息内容样式
        HSSFCellStyle detailStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        //headStyle.Alignment = CellHorizontalAlignment.Center;
        HSSFFont detailfont = (HSSFFont)workbook.CreateFont();
        detailfont.FontHeightInPoints = 9;
        detailfont.Boldweight = 200;
        detailStyle.SetFont(detailfont);
        detailStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; // NPOI.SS.UserModel.BorderStyle.Thin;//下边框
        detailStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;//  NPOI.SS.UserModel.BorderStyle.Thin;//左边框
        detailStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; // NPOI.SS.UserModel.BorderStyle.Thin;//上边框
        detailStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; // NPOI.SS.UserModel.BorderStyle.Thin;//右边框
        detailStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
        int firstrow = 0;//起始行
        int endrow = 0;//起始行
        //保存合计行的数据
        Hashtable tab = new Hashtable();
        foreach (DataRow row in dtSource.Rows)
        {
            if (rowIndex == 65535 || rowIndex == 0)
            {
                if (rowIndex != 0)
                {
                    sheet = (HSSFSheet)workbook.CreateSheet();
                }
                #region 表头标题
                {
                    if (excelSet != null)
                    {
                        if (excelSet.isNeedHeader)
                        {
                            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
                            headerRow.HeightInPoints = 25;
                            //头部名称
                            headerRow.CreateCell(0).SetCellValue(excelSet.strHeaderText);
                            //CellRangeAddress合并单元格四个参数为：起始行，结束行，起始列，结束列
                            CellRangeAddress headregion = new CellRangeAddress(firstrow, endrow, 0, dtSource.Columns.Count - 1);
                            sheet.AddMergedRegion(headregion);
                            if (excelSet.isFreezeHeader)
                            {
                                sheet.CreateFreezePane(0, 1);//冻结窗格
                            }

                            HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                            headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//垂直居中
                            //headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//  NPOI.SS.UserModel.BorderStyle.Thin;//下边框
                            //headStyle.BorderLeft =  NPOI.SS.UserModel.BorderStyle.Thin;//左边框
                            headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;//上边框
                            //headStyle.BorderRight =  NPOI.SS.UserModel.BorderStyle.Thin;//右边框
                            HSSFFont font = (HSSFFont)workbook.CreateFont();
                            font.FontHeightInPoints = excelSet.headerFont;
                            font.Boldweight = 700;
                            headStyle.SetFont(font);
                            //合并单元格后边框的颜色处理
                            for (int i = headregion.FirstRow; i <= headregion.LastRow; i++)
                            {
                                IRow irow = HSSFCellUtil.GetRow(i, sheet);
                                for (int j = headregion.FirstColumn; j <= headregion.LastColumn; j++)
                                {
                                    ICell singleCell = HSSFCellUtil.GetCell(irow, (short)j);
                                    singleCell.CellStyle = headStyle;
                                }
                            }
                            //判断是否需要二级表头
                            if (excelSet.isNeedSecondHeader)
                            {
                                rowIndex += 1;
                                firstrow += 1;
                                endrow += 1;

                                #region 二级表头标题附加信息
                                HSSFRow headerRow2 = (HSSFRow)sheet.CreateRow(rowIndex);
                                headerRow2.HeightInPoints = 20;
                                headerRow2.CreateCell(0).SetCellValue(excelSet.strSecondHeaderText);

                                //CellRangeAddress合并单元格四个参数为：起始行，结束行，起始列，结束列
                                CellRangeAddress region = new CellRangeAddress(firstrow, endrow, 0, dtSource.Columns.Count - 1);
                                sheet.AddMergedRegion(region);
                                if (excelSet.isFreezeSecondHeader)
                                {
                                    sheet.CreateFreezePane(0, 2);//冻结窗格
                                }

                                HSSFCellStyle headStyle2 = (HSSFCellStyle)workbook.CreateCellStyle();
                                headStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//垂直居中
                                headStyle2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//下边框
                                //headStyle2.BorderLeft =  NPOI.SS.UserModel.BorderStyle.Thin;//左边框
                                //headStyle2.BorderTop =  NPOI.SS.UserModel.BorderStyle.Thin;//上边框
                                // headStyle2.BorderRight =  NPOI.SS.UserModel.BorderStyle.Thin;//右边框
                                HSSFFont font2 = (HSSFFont)workbook.CreateFont();
                                font2.FontHeightInPoints = excelSet.secondHeaderFont;
                                font2.Boldweight = 400;
                                headStyle2.SetFont(font2);
                                //合并单元格后边框的颜色处理
                                for (int i = region.FirstRow; i <= region.LastRow; i++)
                                {
                                    IRow irow = HSSFCellUtil.GetRow(i, sheet);
                                    for (int j = region.FirstColumn; j <= region.LastColumn; j++)
                                    {
                                        ICell singleCell = HSSFCellUtil.GetCell(irow, (short)j);
                                        singleCell.CellStyle = headStyle2;
                                    }
                                }
                                #endregion
                            }
                            rowIndex += 1;
                        }
                        else
                        {
                            //firstrow += 1;
                            //endrow += 1;
                        }
                    }
                }
                #endregion

                #region 列头及样式
                {
                    HSSFCellStyle ColumnHeadStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                    //居中，两个必须合起来一起用
                    ColumnHeadStyle.VerticalAlignment = VerticalAlignment.Center;//水平居中
                    ColumnHeadStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//垂直居中
                    HSSFFont columnfont = (HSSFFont)workbook.CreateFont();
                    columnfont.FontHeightInPoints = 10;//字体大小
                    columnfont.Boldweight = 700;//字体的粗细
                    ColumnHeadStyle.SetFont(columnfont);
                    ColumnHeadStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//下边框
                    ColumnHeadStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;//左边框
                    ColumnHeadStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;//上边框
                    ColumnHeadStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//右边框
                    HSSFRow headerRow2 = (HSSFRow)sheet.CreateRow(rowIndex);
                    HSSFRow headerRow3 = new HSSFRow();
                    if (excelSet.isFreezeSecondHeader)
                    {
                        sheet.CreateFreezePane(0, 3);//冻结窗格
                    }
                    if (excelSet.isMergeCell)
                    {
                        if (excelSet.isFreezeSecondHeader)
                        {
                            sheet.CreateFreezePane(0, 4);//冻结窗格
                        }
                        workbook.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3);//有合并列多加一行
                        rowIndex += 1;
                        headerRow3 = (HSSFRow)sheet.CreateRow(rowIndex);
                    }

                    foreach (DataColumn column in dtSource.Columns)
                    {

                        //有多列
                        //#region
                        if (excelSet.isMergeCell)
                        {
                            if (!string.IsNullOrEmpty(excelSet.MergeCellValue))
                            {

                                string flag = "";
                                string ishave = "";

                                string mergeCellname = "";//合并列名称
                                int firstcell;//合并起始列
                                int endcell;//合并起始列
                                string[] s1 = excelSet.MergeCellValue.Split(';');
                                string[] s2;
                                string[] s3;
                                if (s1.Length > 0)
                                {
                                    #region
                                    for (int n = 0; n < s1.Length; n++)
                                    {
                                        s2 = s1[n].Split(':');
                                        mergeCellname = s2[0];
                                        s3 = s2[1].Split(',');
                                        firstcell = int.Parse(s3[0]);
                                        endcell = int.Parse(s3[1]);
                                        if (column.Ordinal >= firstcell && column.Ordinal <= endcell)
                                        {
                                            //CellRangeAddress合并单元格四个参数为：起始行，结束行，起始列，结束列
                                            CellRangeAddress region = new CellRangeAddress(2, 2, firstcell, endcell);
                                            sheet.AddMergedRegion(region);
                                            CellRangeAddress region2 = new CellRangeAddress(3, 3, column.Ordinal, column.Ordinal);
                                            sheet.AddMergedRegion(region2);
                                            headerRow2.CreateCell(column.Ordinal).SetCellValue(ReWriteColumnName(mergeCellname, dtConfig));
                                            headerRow3.CreateCell(column.Ordinal).SetCellValue(ReWriteColumnName(column.ColumnName, dtConfig));
                                            //设置列宽
                                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 300);
                                            //填充样式，边框
                                            for (int i = region.FirstRow; i <= region.LastRow; i++)
                                            {
                                                IRow irow = HSSFCellUtil.GetRow(i, sheet);
                                                for (int j = region.FirstColumn; j <= region.LastColumn; j++)
                                                {
                                                    ICell singleCell = HSSFCellUtil.GetCell(irow, (short)j);
                                                    singleCell.CellStyle = ColumnHeadStyle;
                                                }

                                            }
                                            //填充样式，边框
                                            for (int i = region2.FirstRow; i <= region2.LastRow; i++)
                                            {
                                                IRow irow = HSSFCellUtil.GetRow(i, sheet);
                                                for (int j = region2.FirstColumn; j <= region2.LastColumn; j++)
                                                {
                                                    ICell singleCell = HSSFCellUtil.GetCell(irow, (short)j);
                                                    singleCell.CellStyle = ColumnHeadStyle;
                                                }

                                            }
                                            ishave = "存在值";
                                        }
                                        else
                                        {
                                            flag = "存在列";
                                        }
                                    }
                                    #endregion
                                }
                                else
                                {
                                    //CellRangeAddress合并单元格四个参数为：起始行，结束行，起始列，结束列
                                    CellRangeAddress region = new CellRangeAddress(2, 2, column.Ordinal, column.Ordinal);
                                    sheet.AddMergedRegion(region);
                                    headerRow2.CreateCell(column.Ordinal).SetCellValue(ReWriteColumnName(column.ColumnName, dtConfig));
                                    sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 300);

                                    //填充样式，边框
                                    for (int i = region.FirstRow; i <= region.LastRow; i++)
                                    {
                                        IRow irow = HSSFCellUtil.GetRow(i, sheet);
                                        for (int j = region.FirstColumn; j <= region.LastColumn; j++)
                                        {
                                            ICell singleCell = HSSFCellUtil.GetCell(irow, (short)j);
                                            singleCell.CellStyle = ColumnHeadStyle;
                                        }
                                    }
                                }
                                if (!string.IsNullOrEmpty(flag) && string.IsNullOrEmpty(ishave))
                                {
                                    //CellRangeAddress合并单元格四个参数为：起始行，结束行，起始列，结束列
                                    CellRangeAddress region = new CellRangeAddress(2, 3, column.Ordinal, column.Ordinal);
                                    sheet.AddMergedRegion(region);
                                    headerRow2.CreateCell(column.Ordinal).SetCellValue(ReWriteColumnName(column.ColumnName, dtConfig));
                                    sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 300);

                                    //填充样式，边框
                                    for (int i = region.FirstRow; i <= region.LastRow; i++)
                                    {
                                        IRow irow = HSSFCellUtil.GetRow(i, sheet);
                                        for (int j = region.FirstColumn; j <= region.LastColumn; j++)
                                        {
                                            ICell singleCell = HSSFCellUtil.GetCell(irow, (short)j);
                                            singleCell.CellStyle = ColumnHeadStyle;
                                        }
                                    }
                                }
                            }
                        }
                        else
                        {
                            //CellRangeAddress合并单元格四个参数为：起始行，结束行，起始列，结束列
                            CellRangeAddress region = new CellRangeAddress(2, 2, column.Ordinal, column.Ordinal);
                            sheet.AddMergedRegion(region);
                            headerRow2.CreateCell(column.Ordinal).SetCellValue(ReWriteColumnName(column.ColumnName, dtConfig));
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 300);

                            //填充样式，边框
                            for (int i = region.FirstRow; i <= region.LastRow; i++)
                            {
                                IRow irow = HSSFCellUtil.GetRow(i, sheet);
                                for (int j = region.FirstColumn; j <= region.LastColumn; j++)
                                {
                                    ICell singleCell = HSSFCellUtil.GetCell(irow, (short)j);
                                    singleCell.CellStyle = ColumnHeadStyle;
                                }
                            }
                        }
                    }

                }
                #endregion

                rowIndex += 1;
            }
            #region 填充内容
            HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
            foreach (DataColumn column in dtSource.Columns)
            {
                HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
                newCell.CellStyle = detailStyle;
                string drValue = row[column].ToString();
                #region 合计统计数叠加
                //add by zengtongchun 2016-07-04 合计统计数叠加 开始-----------------
                var str = drValue;
                //int.TryParse(str, out a);
                string text = drValue;
                if (str.Trim() == "0" || str.Trim() == "0.00")
                {
                    text = "";
                }
                int isText = 0;//默认文本
                for (int i = 0; i < dtConfig.Rows.Count; i++)
                {
                    //名称相同并且是数字的
                    if ((column.ToString().Replace("}", "").Replace("{", "").Trim() == dtConfig.Rows[i]["custom_name"].ToString()) && dtConfig.Rows[i]["export_type"].ToString() == "1")
                    {

                        isText = Convert.ToInt32(dtConfig.Rows[i]["export_type"]);
                        break;
                    }
                }
                //数字统计
                if (isText > 0)
                {
                    text = text == "" ? "0" : text;
                    if (tab.ContainsKey(column))
                    {
                        tab[column] = (Convert.ToDecimal(tab[column]) + Convert.ToDecimal(text)).ToString();
                    }
                    else
                    {
                        tab.Add(column, text);
                    }
                }

                //add by zengtongchun 2016-07-04 合计统计数叠加 结束-----------------
                #endregion
                switch (column.DataType.ToString())
                {
                    case "System.String"://字符串类型
                        newCell.SetCellValue(text);
                        break;
                    case "System.DateTime"://日期类型
                        System.DateTime dateV;
                        System.DateTime.TryParse(text, out dateV);
                        newCell.SetCellValue(dateV);

                        newCell.CellStyle = dateStyle;//格式化显示
                        break;
                    case "System.Boolean"://布尔型
                        bool boolV = false;
                        bool.TryParse(text, out boolV);
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16"://整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(text, out intV);
                        if (intV != 0)
                        {
                            newCell.SetCellValue(intV);
                        }

                        break;
                    case "System.Decimal"://浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(text, out doubV);
                        if (doubV != 0)
                        {
                            newCell.SetCellValue(doubV);
                        }
                        break;
                    case "System.DBNull"://空值处理
                        newCell.SetCellValue("");
                        break;
                    default:
                        newCell.SetCellValue("");
                        break;
                }
            }
            #endregion
            rowIndex++;
        }
        //合计行 add by zengtongchun 2016-07-04
        HSSFRow dataRowCount = (HSSFRow)sheet.CreateRow(rowIndex);
        foreach (DataColumn column in dtSource.Columns)
        {
            HSSFCell newCountCell = (HSSFCell)dataRowCount.CreateCell(column.Ordinal);
            newCountCell.CellStyle = detailStyle;
            double drValue = 0;
            if (tab.ContainsKey(column))
            {
                drValue = Convert.ToDouble(tab[column]);

            }
            if (drValue != 0)
            {
                newCountCell.SetCellValue(drValue);
            }

            //switch (column.DataType.ToString())
            //{
            //    case "System.String"://字符串类型
            //        newCountCell.SetCellValue(drValue);
            //        break;
            //    case "System.DateTime"://日期类型
            //        System.DateTime dateV;
            //        System.DateTime.TryParse(drValue, out dateV);
            //        newCountCell.SetCellValue(dateV);

            //        newCountCell.CellStyle = dateStyle;//格式化显示
            //        break;
            //    case "System.Boolean"://布尔型
            //        bool boolV = false;
            //        bool.TryParse(drValue, out boolV);
            //        newCountCell.SetCellValue(boolV);
            //        break;
            //    case "System.Int16"://整型
            //    case "System.Int32":
            //    case "System.Int64":
            //    case "System.Byte":
            //        int intV = 0;
            //        int.TryParse(drValue, out intV);

            //        newCountCell.SetCellValue(intV);
            //        break;
            //    case "System.Decimal"://浮点型
            //    case "System.Double":
            //        double doubV = 0;
            //        double.TryParse(drValue, out doubV);
            //        newCountCell.SetCellValue(doubV);
            //        break;
            //    case "System.DBNull"://空值处理
            //        newCountCell.SetCellValue("");
            //        break;
            //    default:
            //        newCountCell.SetCellValue("");
            //        break;
            //}
        }
        //合计行 add by zengtongchun 2016-07-04

        using (MemoryStream ms = new MemoryStream())
        {
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            //sheet.Dispose();
            //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
            return ms;
        }
    }



    /// <summary>
    /// DataTable导出到Excel的MemoryStream
    /// </summary>
    /// <param name="myDgv">源DataTable</param>
    /// <param name="strHeaderText">表头文本</param>
    public static MemoryStream DataGridViewToExcel(DataGridView myDgv, string strHeaderText)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();

        #region 右击文件 属性信息
        {
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI";
            workbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = "文件作者信息"; //填加xls文件作者信息
            si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
            si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
            si.Comments = "作者信息"; //填加xls文件作者信息
            si.Title = "标题信息"; //填加xls文件标题信息
            si.Subject = "主题信息";//填加文件主题信息
            si.CreateDateTime = System.DateTime.Now;
            workbook.SummaryInformation = si;
        }
        #endregion

        HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

        //取得列宽
        int[] arrColWidth = new int[myDgv.Columns.Count];
        foreach (DataGridViewColumn item in myDgv.Columns)
        {
            arrColWidth[item.Index] = Encoding.GetEncoding(936).GetBytes(item.HeaderText.ToString()).Length;
        }
        for (int i = 0; i < myDgv.Rows.Count; i++)
        {
            for (int j = 0; j < myDgv.Columns.Count; j++)
            {
                int intTemp = Encoding.GetEncoding(936).GetBytes(myDgv.Rows[i].Cells[j].ToString()).Length;
                if (intTemp > arrColWidth[j])
                {
                    arrColWidth[j] = intTemp;
                }
            }
        }
        int rowIndex = 0;
        //添加信息内容样式
        HSSFCellStyle detailStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        //headStyle.Alignment = CellHorizontalAlignment.Center;
        HSSFFont detailfont = (HSSFFont)workbook.CreateFont();
        detailfont.FontHeightInPoints = 9;
        detailfont.Boldweight = 700;
        detailfont.FontName = "宋体";
        detailStyle.SetFont(detailfont);
        foreach (DataGridViewRow row in myDgv.Rows)
        {
            #region 新建表，填充表头，填充列头，样式
            if (rowIndex == 65535 || rowIndex == 0)
            {
                if (rowIndex != 0)
                {
                    sheet = (HSSFSheet)workbook.CreateSheet();
                }

                #region 表头及样式
                {
                    #region 表头标题
                    HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
                    headerRow.HeightInPoints = 25;//行高
                    headerRow.CreateCell(0).SetCellValue(strHeaderText);
                    //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 26));
                    ////设置单元格的样式：水平对齐居中
                    //ICellStyle style = workbook.CreateCellStyle();
                    ////IRow irow = sheet.CreateRow(0);
                    ////ICell cell = headerRow.CreateCell(2);
                    //style.VerticalAlignment = VerticalAlignment.Center;//垂直居中
                    ////style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    //headerRow.GetCell(0).CellStyle = style;

                    ////设置单元格的高度
                    //row.Height = 30 * 20;
                    ////设置单元格的宽度
                    //sheet.SetColumnWidth(0, 30 * 256);
                    HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//垂直居中
                    HSSFFont font = (HSSFFont)workbook.CreateFont();
                    font.FontHeightInPoints = 20;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    headerRow.GetCell(0).CellStyle = headStyle;
                    // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                    //headerRow.Dispose();
                    #endregion

                    #region 表头标题附加信息
                    HSSFRow headerRow2 = (HSSFRow)sheet.CreateRow(1);
                    headerRow2.HeightInPoints = 20;
                    headerRow2.CreateCell(0).SetCellValue("客户:TCL海外  ()" + "第二客户：" + "联系人:王婕" + "Tel(Fax):0755-26852196" + "结算单号:JSHYHW2016061700021" + "计费员:林媛媛");

                    //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                    sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 26));

                    HSSFCellStyle headStyle2 = (HSSFCellStyle)workbook.CreateCellStyle();
                    headStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//垂直居中
                    HSSFFont font2 = (HSSFFont)workbook.CreateFont();
                    font2.FontHeightInPoints = 12;
                    font2.Boldweight = 400;
                    headStyle2.SetFont(font2);
                    headerRow2.GetCell(0).CellStyle = headStyle2;
                    #endregion
                }
                #endregion


                #region 列头及样式
                {
                    HSSFRow headerRow = (HSSFRow)sheet.CreateRow(2);
                    HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                    //headStyle.Alignment = CellHorizontalAlignment.Center;
                    HSSFFont font = (HSSFFont)workbook.CreateFont();
                    font.FontHeightInPoints = 10;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    foreach (DataGridViewColumn column in myDgv.Columns)
                    {
                        headerRow.CreateCell(column.Index).SetCellValue(column.HeaderText);
                        headerRow.GetCell(column.Index).CellStyle = headStyle;

                        //设置列宽
                        sheet.SetColumnWidth(column.Index, (arrColWidth[column.Index] + 1) * 256);
                    }
                    // headerRow.Dispose();
                }
                #endregion

                rowIndex = 3;
            }
            #endregion


            #region 填充内容
            HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
            if (row.Index > 0)
            {
                foreach (DataGridViewColumn column in myDgv.Columns)
                {
                    HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Index);
                    newCell.CellStyle = detailStyle;

                    string drValue = myDgv[column.Index, row.Index - 1].Value.ToString();

                    switch (column.ValueType.ToString())
                    {
                        case "System.String"://字符串类型
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://日期类型
                            System.DateTime dateV;
                            System.DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);

                            newCell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }
            }
            else
            { rowIndex--; }
            #endregion

            rowIndex++;
        }
        using (MemoryStream ms = new MemoryStream())
        {
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            //sheet.Dispose();
            //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
            return ms;
        }
    }
    /// <summary>
    /// 导出表头设置
    /// </summary>
    /// <param name="configDT">配置数据</param>
    /// <param name="sheetName">页签名称</param>
    /// <param name="Caption">表头</param>
    /// <param name="isNeedSecondCaption">是否需要2表头</param>
    /// <param name="SecondCaption">第二表头</param>
    /// <param name="isFreezeHeader">固定表头</param>
    /// <returns></returns>
    public static ExcelSet ConvertConfig1(DataTable configDT, string sheetName, string Caption, bool isNeedSecondCaption, string SecondCaption, bool isFreezeHeader)
    {
        StringBuilder mergeCellValue = new StringBuilder();
        string caption = "";
        int fromCol = 0;//合并开始列
        int toCol = 0;//合并结束列
        int index = 0;
        foreach (DataRow row in configDT.Rows)
        {
            row["sort"] = index;
            index = index + 1;
        }
        foreach (DataRow row in configDT.Rows)
        {
            string mergeName = row["merge_name"] as string;

            int sort = int.Parse(row["sort"].ToString());
            
            //是合并的
            if (caption == mergeName && !string.IsNullOrEmpty(mergeName) && !string.IsNullOrEmpty(caption))
            {
                toCol = sort;
                if (mergeCellValue.Length > 0)
                {
                    mergeCellValue.Append(string.Format(";{0}:{1},{2}", caption, fromCol, toCol));
                }
                else
                {
                    mergeCellValue.Append(string.Format("{0}:{1},{2}", caption, fromCol, toCol));
                }
                fromCol = 0;//初始化开始
                caption = "";
                continue;
            }
            if (!string.IsNullOrEmpty(mergeName))
            {
                if (fromCol == 0)
                {
                    fromCol = sort;//当前序号
                }
                caption = mergeName;
               
            }

        }
        ExcelSet excelSet = new ExcelSet
        {
            sheetName = sheetName,
            isNeedHeader = true,//是否需要显示表头
            strHeaderText = Caption,
            isFreezeHeader = isFreezeHeader,
            headerFont = 12,
            isNeedSecondHeader = isNeedSecondCaption,
            strSecondHeaderText = SecondCaption,//"客户:TCL海外  ()" + "第二客户：" + "联系人:王婕" + "Tel(Fax):0755-26852196" + "结算单号:JSHYHW2016061700021" + "计费员:林媛媛",
            isFreezeSecondHeader = isFreezeHeader,
            secondHeaderFont = 11,
            isMergeCell = !string.IsNullOrEmpty(mergeCellValue.ToString()),
            MergeCellValue = mergeCellValue.ToString()//"合并輎:2,4;ha:6,8"

        };
        return excelSet;
    }

    /// <summary>
    /// 导出表头设置
    /// </summary>
    /// <param name="configDT">配置数据</param>
    /// <param name="sheetName">页签名称</param>
    /// <param name="Caption">表头</param>
    /// <param name="isNeedSecondCaption">是否需要2表头</param>
    /// <param name="SecondCaption">第二表头</param>
    /// <param name="isFreezeHeader">固定表头</param>
    /// <returns></returns>
    public static ExcelSet ConvertConfig(DataTable configDT, string sheetName, string Caption, bool isNeedSecondCaption,string SecondCaption, bool isFreezeHeader)
    {
        StringBuilder mergeCellValue = new StringBuilder();
        string caption = "";
        int fromCol = 0;//合并开始列
        int toCol = 0;//合并结束列
        int index = 0;
        foreach (DataRow row in configDT.Rows)
        {
            row["sort"] = index;
            index = index + 1;
        }
        foreach(DataRow row in configDT.Rows)
        {
            string mergeName  =  row["merge_name"] as string;














            //二个都是空，跳过
            if (string.IsNullOrEmpty(caption) && caption == mergeName)
            {
                fromCol = 0;
                toCol = 0;
                continue;
            }

            //caption为空，mergeName不为空，初始化数据
            if (string.IsNullOrEmpty(caption) && caption != mergeName)
            {
                if (fromCol == 0)
                {
                    fromCol = int.Parse(row["sort"].ToString());
                    toCol = int.Parse(row["sort"].ToString());// as int;
                }
                caption = mergeName;
            }


            //caption不为空，且caption==mergeName
            if (!string.IsNullOrEmpty(caption) && caption == mergeName)
            {
                //fromCol = 0;
                toCol = int.Parse(row["sort"].ToString());// as int;
                continue;
            }


            /*合并结束*/
            if (!string.IsNullOrEmpty(caption) && caption != mergeName)
            {
                if (mergeCellValue.Length > 0)
                {
                    mergeCellValue.Append(string.Format(";{0}:{1},{2}", caption, fromCol, toCol));
                }
                else
                {
                    mergeCellValue.Append(string.Format("{0}:{1},{2}", caption, fromCol, toCol));
                }

                if (string.IsNullOrEmpty(mergeName))
                {
                    fromCol = 0;
                    toCol = 0;
                }
                else
                {
                    //if (fromCol == 0)
                    fromCol = int.Parse(row["sort"].ToString());
                    toCol = int.Parse(row["sort"].ToString());// as int;
                }
                caption = mergeName;
            }
            if (!string.IsNullOrEmpty(caption))
            {
                if (mergeCellValue.Length > 0)
                {
                    mergeCellValue.Append(string.Format(";{0}:{1},{2}", caption, fromCol, toCol));
                }
                else
                {
                    mergeCellValue.Append(string.Format("{0}:{1},{2}", caption, fromCol, toCol));
                }
            }
        }
        ExcelSet excelSet = new ExcelSet
        {
            sheetName = sheetName,
            isNeedHeader = true,//是否需要显示表头
            strHeaderText = Caption,
            isFreezeHeader = isFreezeHeader,
            headerFont = 12,
            isNeedSecondHeader = isNeedSecondCaption,
            strSecondHeaderText = SecondCaption,//"客户:TCL海外  ()" + "第二客户：" + "联系人:王婕" + "Tel(Fax):0755-26852196" + "结算单号:JSHYHW2016061700021" + "计费员:林媛媛",
            isFreezeSecondHeader = isFreezeHeader,
            secondHeaderFont = 11,
            isMergeCell = !string.IsNullOrEmpty(mergeCellValue.ToString()),
            MergeCellValue = mergeCellValue.ToString()//"合并輎:2,4;ha:6,8"

        };
        return excelSet;
    }

    /*  sample:
     *  ExcelSet excelSet = new ExcelSet
            {
                sheetName = "单据名称",
                isNeedHeader = true,//是否需要显示表头
                strHeaderText = "华商联报关公司",
                isFreezeHeader = true,
                headerFont = 20,
                isNeedSecondHeader = true,
                strSecondHeaderText = "客户:TCL海外  ()" + "第二客户：" + "联系人:王婕" + "Tel(Fax):0755-26852196" + "结算单号:JSHYHW2016061700021" + "计费员:林媛媛",
                isFreezeSecondHeader = true,
                secondHeaderFont = 14,
                isMergeCell = true,
                MergeCellValue = "合并輎:2,4;ha:6,8"

            };
            System.Diagnostics.Stopwatch timeWatch = System.Diagnostics.Stopwatch.StartNew();
            // ExcelHelper.DatagridviewToExcel(dataGridView1, "D:/AA.xlsx");
            string[,] MergeCellValue = { { "合并名称", "2", "3" } };
            string head2string = "客户:TCL海外  ()" + "第二客户：" + "联系人:王婕" + "Tel(Fax):0755-26852196" + "结算单号:JSHYHW2016061700021" + "计费员:林媛媛";
            //NPOIExcelHelper.DataTableToExcel(dt, "测试", head2string, MergeCellValue, "D:/测试.xls");
            NPOIExcelHelper.DataTableToExcel(dt, excelSet, "D:/测试.xls");
            //NPOIExcelHelper.DataGridViewToExcel(dataGridView1, "测试", "D:/mytest1.xls");
            timeWatch.Stop();
            MessageBox.Show("总共耗时" + timeWatch.Elapsed);
    */


    /// <summary>读取excel
    /// 默认第一行为标头
    /// </summary>
    /// <param name="strFileName">excel文档路径</param>
    /// <returns></returns>
    public static DataTable Import(string strFileName)
    {
        DataTable dt = new DataTable();

        HSSFWorkbook hssfworkbook;
        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
        {
            hssfworkbook = new HSSFWorkbook(file);
        }
        HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

        HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
        int cellCount = headerRow.LastCellNum;

        for (int j = 0; j < cellCount; j++)
        {
            HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
            dt.Columns.Add(cell.ToString());
        }

        for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
        {
            HSSFRow row = (HSSFRow)sheet.GetRow(i);
            DataRow dataRow = dt.NewRow();

            for (int j = row.FirstCellNum; j < cellCount; j++)
            {
                if (row.GetCell(j) != null)
                    dataRow[j] = row.GetCell(j).ToString();
            }

            dt.Rows.Add(dataRow);
        }
        return dt;
    }
}

}